package com.fcscs.ecms.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.domain.ThemeItem;
import com.fcscs.cservice.ws.WebServiceUtils;
import com.fcscs.ecms.dao.ECMSThemeDao;


@SuppressWarnings("unchecked")
@Repository("ecmsThemeDao")
public class ECMSThemeDaoHibernate implements ECMSThemeDao {

	private static DBLoc TBLoc = new ThemeItem().getDBLoc();

	@Autowired
	private ConnectionManager connMgr;


	private static final String FIND_THEME_LIST = "From ThemeItem ti where ti.orgId=:orgId and ti.propertyId=:propId ";

	private static final String THEME_NAME_CNT  = "select count(*) from theme_item where org_id = :orgId and " +
	" property_id = :propId and name = :name and id <> :id and active = true ";
	
	
	@Override
	public ThemeItem getThemeItem(long id) {
		return (ThemeItem) connMgr.getCurrentSession(TBLoc).get(ThemeItem.class,id);
	}

	@Override
	public List<ThemeItem> getThemeItemList(long orgId, long propId,boolean isCurrent) {

		Query  q = connMgr.getCurrentSession(TBLoc).createQuery(FIND_THEME_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);

		return q.list();
	}

	@Override
	public List<ThemeItem> getThemeItemList(long orgId, long propId,long versionId) {

		Query  q = connMgr.getCurrentSession(TBLoc).createQuery(FIND_THEME_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);

		return q.list();
	}

	@Override
	public List<ThemeItem> saveThemeItemList(List<ThemeItem> tiList) {

		Session session  = connMgr.getCurrentSession(TBLoc);
		for(ThemeItem th : tiList){
			session.saveOrUpdate(th);
		}
		return tiList;
	}

	@Override
	public ThemeItem createThemeItem(ThemeItem ti) {
			connMgr.getCurrentSession(TBLoc).save(ti);
		return ti;
	}

	@Override
	public ThemeItem saveThemeItem(ThemeItem ti) {
		connMgr.getCurrentSession(TBLoc).saveOrUpdate(ti);
		return ti ;
	}

	@Override
	public boolean deleteThemeItem(ThemeItem ti) {
		
		String sql = "Update ThemeItem sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(TBLoc).createQuery(sql);
		q.setLong("id", ti.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteThemeItem(ThemeItem ti) {
		try {
			connMgr.getCurrentSession(TBLoc).delete(ti);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	@Override
	public boolean isThemeItemTaken(long orgId, long propId, String name,Long id, long versionId) {
		
		Query q  = connMgr.getCurrentSession(TBLoc).createSQLQuery(THEME_NAME_CNT);
		q.setString("name", name);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);
		
		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<com.fcscs.cservice.domain.model.ThemeItem> getThemeItemViewList(long orgId, long propId) {
		
		String sql = "select t.id, t.name, t.active, i.image from ThemeItem t ,ImageResource i " +
				"where t.imageId=i.id and t.orgId=:orgId and t.propertyId=:propId";
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createQuery(sql);
		sqlQu.setParameter("orgId", orgId);
		sqlQu.setParameter("propId", orgId);
		
		List<com.fcscs.cservice.domain.model.ThemeItem> list = new ArrayList<com.fcscs.cservice.domain.model.ThemeItem>();
		List<Object[]> categories = (List<Object[]>)sqlQu.list();
		for(Object[] o : categories) {
			 com.fcscs.cservice.domain.model.ThemeItem theme = new com.fcscs.cservice.domain.model.ThemeItem();
			 theme.setThemeId((Long) o[0]);
			 theme.setName((String) o[1]);
			 theme.setActive((Boolean) o[2]);
			 theme.setImageFile(WebServiceUtils.convertToString((byte[])o[3]));
			 list.add(theme);
		 }
		
		return list;
	}

}
